"""
新闻数据访问层 (DAO)
提供新闻相关数据的专用访问接口
"""

from typing import List, Optional, Dict, Any, Tuple
from datetime import datetime, date, timedelta
from sqlalchemy import select, and_, or_, desc, asc, func, text
from sqlalchemy.ext.asyncio import AsyncSession

from .base import FinancialDataDAO
from ..models.news import (
    NewsArticle,
    NewsEvent,
    NewsResearch,
    NewsAnnouncement
)


class NewsArticleDAO(FinancialDataDAO[NewsArticle]):
    """新闻文章数据访问层"""
    
    model = NewsArticle
    
    async def get_by_source(
        self,
        session: AsyncSession,
        source: str,
        limit: int = 100
    ) -> List[NewsArticle]:
        """根据新闻源获取文章"""
        stmt = (
            select(self.model)
            .where(self.model.source == source)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_category(
        self,
        session: AsyncSession,
        category: str,
        limit: int = 100
    ) -> List[NewsArticle]:
        """根据分类获取新闻"""
        stmt = (
            select(self.model)
            .where(self.model.category == category)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_stock_codes(
        self,
        session: AsyncSession,
        stock_codes: List[str],
        start_date: Optional[datetime] = None,
        end_date: Optional[datetime] = None,
        limit: int = 100
    ) -> List[NewsArticle]:
        """根据股票代码获取相关新闻"""
        conditions = []
        
        # 使用PostgreSQL的数组操作符检查股票代码
        for stock_code in stock_codes:
            conditions.append(
                text(f"'{stock_code}' = ANY(related_stocks)")
            )
        
        if conditions:
            stock_condition = or_(*conditions)
        else:
            return []
        
        query_conditions = [stock_condition]
        
        if start_date:
            query_conditions.append(self.model.publish_time >= start_date)
        if end_date:
            query_conditions.append(self.model.publish_time <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*query_conditions))
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_recent_news(
        self,
        session: AsyncSession,
        hours: int = 24,
        limit: int = 100
    ) -> List[NewsArticle]:
        """获取最近N小时的新闻"""
        from_time = datetime.now() - timedelta(hours=hours)
        
        stmt = (
            select(self.model)
            .where(self.model.publish_time >= from_time)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_hot_news(
        self,
        session: AsyncSession,
        limit: int = 20
    ) -> List[NewsArticle]:
        """获取热点新闻"""
        stmt = (
            select(self.model)
            .where(self.model.is_hot == True)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_top_news(
        self,
        session: AsyncSession,
        limit: int = 20
    ) -> List[NewsArticle]:
        """获取置顶新闻"""
        stmt = (
            select(self.model)
            .where(self.model.is_top == True)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def search_news(
        self,
        session: AsyncSession,
        keyword: str,
        start_date: Optional[datetime] = None,
        end_date: Optional[datetime] = None,
        category: Optional[str] = None,
        source: Optional[str] = None,
        limit: int = 100
    ) -> List[NewsArticle]:
        """搜索新闻"""
        conditions = [
            or_(
                self.model.title.ilike(f'%{keyword}%'),
                self.model.content.ilike(f'%{keyword}%'),
                self.model.summary.ilike(f'%{keyword}%')
            )
        ]
        
        if start_date:
            conditions.append(self.model.publish_time >= start_date)
        if end_date:
            conditions.append(self.model.publish_time <= end_date)
        if category:
            conditions.append(self.model.category == category)
        if source:
            conditions.append(self.model.source == source)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_news_by_sentiment(
        self,
        session: AsyncSession,
        sentiment: str,  # '正面', '负面', '中性'
        limit: int = 100
    ) -> List[NewsArticle]:
        """根据情感倾向获取新闻"""
        stmt = (
            select(self.model)
            .where(self.model.sentiment == sentiment)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_high_importance_news(
        self,
        session: AsyncSession,
        min_importance: int = 4,
        limit: int = 50
    ) -> List[NewsArticle]:
        """获取高重要性新闻"""
        stmt = (
            select(self.model)
            .where(self.model.importance_level >= min_importance)
            .order_by(desc(self.model.importance_level), desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_news_statistics(
        self,
        session: AsyncSession,
        start_date: datetime,
        end_date: datetime
    ) -> Dict[str, Any]:
        """获取新闻统计信息"""
        # 总新闻数
        total_count = await session.scalar(
            select(func.count(self.model.news_id))
            .where(
                and_(
                    self.model.publish_time >= start_date,
                    self.model.publish_time <= end_date
                )
            )
        )
        
        # 按分类统计
        category_stats = await session.execute(
            select(self.model.category, func.count(self.model.news_id))
            .where(
                and_(
                    self.model.publish_time >= start_date,
                    self.model.publish_time <= end_date
                )
            )
            .group_by(self.model.category)
        )
        
        # 按来源统计
        source_stats = await session.execute(
            select(self.model.source, func.count(self.model.news_id))
            .where(
                and_(
                    self.model.publish_time >= start_date,
                    self.model.publish_time <= end_date
                )
            )
            .group_by(self.model.source)
        )
        
        # 按情感统计
        sentiment_stats = await session.execute(
            select(self.model.sentiment, func.count(self.model.news_id))
            .where(
                and_(
                    self.model.publish_time >= start_date,
                    self.model.publish_time <= end_date
                )
            )
            .group_by(self.model.sentiment)
        )
        
        # 热点新闻数
        hot_count = await session.scalar(
            select(func.count(self.model.news_id))
            .where(
                and_(
                    self.model.publish_time >= start_date,
                    self.model.publish_time <= end_date,
                    self.model.is_hot == True
                )
            )
        )
        
        return {
            'total_count': total_count or 0,
            'hot_count': hot_count or 0,
            'by_category': dict(category_stats.all()),
            'by_source': dict(source_stats.all()),
            'by_sentiment': dict(sentiment_stats.all())
        }
    
    async def get_trending_topics(
        self,
        session: AsyncSession,
        hours: int = 24,
        limit: int = 10
    ) -> List[Dict[str, Any]]:
        """获取热门话题"""
        from_time = datetime.now() - timedelta(hours=hours)
        
        # 这里简化实现，实际可能需要更复杂的文本分析
        # 统计关键词出现频率
        stmt = (
            select(self.model.title, self.model.summary)
            .where(self.model.publish_time >= from_time)
        )
        result = await session.execute(stmt)
        articles = result.all()
        
        # 简单的关键词提取（实际应该使用NLP技术）
        word_count = {}
        for title, summary in articles:
            # 这里只是示例，实际需要更好的中文分词
            words = (title or '').split() + (summary or '').split()
            for word in words:
                if len(word) > 1:  # 过滤单字符
                    word_count[word] = word_count.get(word, 0) + 1
        
        # 排序并返回前N个
        trending = sorted(word_count.items(), key=lambda x: x[1], reverse=True)[:limit]
        
        return [
            {'keyword': word, 'count': count}
            for word, count in trending
        ]
    
    async def get_market_sentiment_trend(
        self,
        session: AsyncSession,
        days: int = 7
    ) -> List[Dict[str, Any]]:
        """获取市场情感趋势"""
        from_date = datetime.now().date() - timedelta(days=days)
        
        # 按日期统计情感分布
        stmt = (
            select(
                func.date(self.model.publish_time).label('date'),
                self.model.sentiment,
                func.count(self.model.news_id).label('count'),
                func.avg(self.model.sentiment_score).label('avg_score')
            )
            .where(func.date(self.model.publish_time) >= from_date)
            .group_by(func.date(self.model.publish_time), self.model.sentiment)
            .order_by(func.date(self.model.publish_time))
        )
        
        result = await session.execute(stmt)
        sentiment_data = result.all()
        
        # 组织数据
        trend_data = {}
        for date, sentiment, count, avg_score in sentiment_data:
            date_str = date.strftime('%Y-%m-%d')
            if date_str not in trend_data:
                trend_data[date_str] = {}
            trend_data[date_str][sentiment] = {
                'count': count,
                'avg_score': float(avg_score or 0)
            }
        
        return [
            {'date': date, 'sentiments': sentiments}
            for date, sentiments in trend_data.items()
        ]


class NewsEventDAO(FinancialDataDAO[NewsEvent]):
    """新闻事件数据访问层"""
    
    model = NewsEvent
    
    async def get_by_type(
        self,
        session: AsyncSession,
        event_type: str,
        limit: int = 100
    ) -> List[NewsEvent]:
        """根据事件类型获取事件"""
        stmt = (
            select(self.model)
            .where(self.model.event_type == event_type)
            .order_by(desc(self.model.event_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_level(
        self,
        session: AsyncSession,
        min_level: int,
        limit: int = 100
    ) -> List[NewsEvent]:
        """根据事件级别获取事件"""
        stmt = (
            select(self.model)
            .where(self.model.event_level >= min_level)
            .order_by(desc(self.model.event_level), desc(self.model.event_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_status(
        self,
        session: AsyncSession,
        status: str,
        limit: int = 100
    ) -> List[NewsEvent]:
        """根据事件状态获取事件"""
        stmt = (
            select(self.model)
            .where(self.model.status == status)
            .order_by(desc(self.model.event_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_upcoming_events(
        self,
        session: AsyncSession,
        days: int = 7,
        limit: int = 50
    ) -> List[NewsEvent]:
        """获取即将发生的事件"""
        from_time = datetime.now()
        to_time = from_time + timedelta(days=days)
        
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.expected_time.isnot(None),
                    self.model.expected_time >= from_time,
                    self.model.expected_time <= to_time,
                    self.model.status.in_(['预告', '进行中'])
                )
            )
            .order_by(self.model.expected_time)
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_stock_codes(
        self,
        session: AsyncSession,
        stock_codes: List[str],
        start_date: Optional[datetime] = None,
        end_date: Optional[datetime] = None,
        limit: int = 100
    ) -> List[NewsEvent]:
        """根据股票代码获取相关事件"""
        conditions = []
        
        # 使用PostgreSQL的数组操作符检查股票代码
        for stock_code in stock_codes:
            conditions.append(
                text(f"'{stock_code}' = ANY(related_stocks)")
            )
        
        if conditions:
            stock_condition = or_(*conditions)
        else:
            return []
        
        query_conditions = [stock_condition]
        
        if start_date:
            query_conditions.append(self.model.event_time >= start_date)
        if end_date:
            query_conditions.append(self.model.event_time <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*query_conditions))
            .order_by(desc(self.model.event_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())


class NewsResearchDAO(FinancialDataDAO[NewsResearch]):
    """研究报告数据访问层"""
    
    model = NewsResearch
    
    async def get_by_institution(
        self,
        session: AsyncSession,
        institution: str,
        limit: int = 100
    ) -> List[NewsResearch]:
        """根据研究机构获取报告"""
        stmt = (
            select(self.model)
            .where(self.model.institution == institution)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_analyst(
        self,
        session: AsyncSession,
        analyst: str,
        limit: int = 100
    ) -> List[NewsResearch]:
        """根据分析师获取报告"""
        stmt = (
            select(self.model)
            .where(self.model.analyst == analyst)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_stock_code(
        self,
        session: AsyncSession,
        stock_code: str,
        limit: int = 100
    ) -> List[NewsResearch]:
        """根据股票代码获取研究报告"""
        stmt = (
            select(self.model)
            .where(self.model.stock_code == stock_code)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_rating(
        self,
        session: AsyncSession,
        rating: str,
        limit: int = 100
    ) -> List[NewsResearch]:
        """根据投资评级获取报告"""
        stmt = (
            select(self.model)
            .where(self.model.rating == rating)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_rating_changes(
        self,
        session: AsyncSession,
        days: int = 30,
        limit: int = 100
    ) -> List[NewsResearch]:
        """获取评级变化的报告"""
        from_date = datetime.now() - timedelta(days=days)
        
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.publish_time >= from_date,
                    self.model.previous_rating.isnot(None),
                    self.model.rating != self.model.previous_rating
                )
            )
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())


class NewsAnnouncementDAO(FinancialDataDAO[NewsAnnouncement]):
    """公告数据访问层"""
    
    model = NewsAnnouncement
    
    async def get_by_stock_code(
        self,
        session: AsyncSession,
        stock_code: str,
        limit: int = 100
    ) -> List[NewsAnnouncement]:
        """根据股票代码获取公告"""
        stmt = (
            select(self.model)
            .where(self.model.stock_code == stock_code)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_type(
        self,
        session: AsyncSession,
        announcement_type: str,
        limit: int = 100
    ) -> List[NewsAnnouncement]:
        """根据公告类型获取公告"""
        stmt = (
            select(self.model)
            .where(self.model.announcement_type == announcement_type)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_major_events(
        self,
        session: AsyncSession,
        limit: int = 100
    ) -> List[NewsAnnouncement]:
        """获取重大事项公告"""
        stmt = (
            select(self.model)
            .where(self.model.is_major_event == True)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_importance(
        self,
        session: AsyncSession,
        min_importance: int,
        limit: int = 100
    ) -> List[NewsAnnouncement]:
        """根据重要程度获取公告"""
        stmt = (
            select(self.model)
            .where(self.model.importance_level >= min_importance)
            .order_by(desc(self.model.importance_level), desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_recent_announcements(
        self,
        session: AsyncSession,
        days: int = 7,
        limit: int = 100
    ) -> List[NewsAnnouncement]:
        """获取最近的公告"""
        from_date = datetime.now() - timedelta(days=days)
        
        stmt = (
            select(self.model)
            .where(self.model.publish_time >= from_date)
            .order_by(desc(self.model.publish_time))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())